/**********************************************************************/
/* InstallMembership.SQL - SCHEMA REVAMP VERSION                      */
/*                                                                    */
/* Installs the tables, triggers and stored procedures necessary for  */
/* supporting the aspnet feature of ASP.Net                           */
/*                                                                    */
/* InstallCommon.sql must be run before running this file.            */
/*                                                                    */
/* Copyright Microsoft, Inc. 2002                                     */
/* All Rights Reserved.                                               */
/*                                                                    */
/**********************************************************************/

PRINT '-------------------------------------------'
PRINT 'Starting execution of InstallMembership.SQL'
PRINT '-------------------------------------------'
GO

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/

DECLARE @dbname nvarchar(128)

SET @dbname = N'aspnetdb'

IF (NOT EXISTS (SELECT name
				FROM master.dbo.sysdatabases
				WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
  RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
END
GO

USE [aspnetdb]
GO

IF (NOT EXISTS (SELECT SCHEMA_NAME
				FROM INFORMATION_SCHEMA.SCHEMATA
				WHERE SCHEMA_NAME = N'aspnet'))
BEGIN
  RAISERROR('The schema ''aspnet'' cannot be found. Please run InstallCommon.sql first.', 18, 1)
END

IF (NOT EXISTS (SELECT TABLE_NAME
				FROM INFORMATION_SCHEMA.TABLES
				WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Applications')))
BEGIN
  RAISERROR('The table ''aspnet.Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT TABLE_NAME
				FROM INFORMATION_SCHEMA.TABLES
				WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Users')))
BEGIN
  RAISERROR('The table ''aspnet.Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Applications_CreateApplication')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Users_CreateUser')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

IF (NOT EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Users_DeleteUser')))
BEGIN
  RAISERROR('The stored procedure ''aspnet.Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END

/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT TABLE_NAME
				FROM INFORMATION_SCHEMA.TABLES
				WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'Membership')))
BEGIN
  PRINT 'Creating the aspnet.Membership table...'
  CREATE TABLE aspnet.Membership (
		ApplicationId                           uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES aspnet.Applications(ApplicationId),
		UserId                                  uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES aspnet.Users(UserId),
		Password                                nvarchar(128)       NOT NULL,
		PasswordFormat                          int                 NOT NULL DEFAULT 0,
		PasswordSalt                            nvarchar(128)       NOT NULL,
		MobilePIN                               nvarchar(16),
		Email                                   nvarchar(256),
		LoweredEmail                            nvarchar(256),
		PasswordQuestion                        nvarchar(256),
		PasswordAnswer                          nvarchar(128),
		IsApproved                              bit                 NOT NULL,
		IsLockedOut                             bit                 NOT NULL,
		CreateDate                              datetime            NOT NULL,
		LastLoginDate                           datetime            NOT NULL,
		LastPasswordChangedDate                 datetime            NOT NULL,
		LastLockoutDate                         datetime            NOT NULL,
		FailedPasswordAttemptCount              int                 NOT NULL,
		FailedPasswordAttemptWindowStart        datetime            NOT NULL,
		FailedPasswordAnswerAttemptCount        int                 NOT NULL,
		FailedPasswordAnswerAttemptWindowStart  datetime            NOT NULL,
		Comment                                 ntext )
  CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet.Membership(ApplicationId, LoweredEmail)
END
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/

DECLARE @ver int
DECLARE @version nchar(100)
DECLARE @dot int
DECLARE @hyphen int
DECLARE @SqlToExec nchar(400)

SELECT @ver = 8
SELECT @version = @@Version
SELECT @hyphen  = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
	SELECT @hyphen = @hyphen + 3
	SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
	IF (NOT(@dot IS NULL) AND @dot > @hyphen)
	BEGIN
		SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
		SELECT @ver     = CONVERT(int, @version)
	END
END

/*************************************************************/

IF (@ver >= 8)
	EXEC sp_tableoption N'aspnet.Membership', 'text in row', 3000

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_CreateUser')))
DROP PROCEDURE aspnet.Membership_CreateUser
GO

CREATE PROCEDURE aspnet.Membership_CreateUser
	@ApplicationName                        nvarchar(256),
	@UserName                               nvarchar(256),
	@Password                               nvarchar(128),
	@PasswordSalt                           nvarchar(128),
	@Email                                  nvarchar(256),
	@PasswordQuestion                       nvarchar(256),
	@PasswordAnswer                         nvarchar(128),
	@IsApproved                             bit,
	@CurrentTimeUtc                         datetime,
	@CreateDate                             datetime = NULL,
	@UniqueEmail                            int      = 0,
	@PasswordFormat                         int      = 0,
	@UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ApplicationId uniqueidentifier
	SELECT  @ApplicationId = NULL

	DECLARE @NewUserId uniqueidentifier
	SELECT @NewUserId = NULL

	DECLARE @IsLockedOut bit
	SET @IsLockedOut = 0

	DECLARE @LastLockoutDate  datetime
	SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

	DECLARE @FailedPasswordAttemptCount int
	SET @FailedPasswordAttemptCount = 0

	DECLARE @FailedPasswordAttemptWindowStart  datetime
	SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

	DECLARE @FailedPasswordAnswerAttemptCount int
	SET @FailedPasswordAnswerAttemptCount = 0

	DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
	SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

	DECLARE @NewUserCreated bit
	DECLARE @ReturnValue   int
	SET @ReturnValue = 0

	DECLARE @ErrorCode     int
	SET @ErrorCode = 0

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
		SET @TranStarted = 0

	EXEC aspnet.Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	IF( @@ERROR <> 0 )
	BEGIN
		SET @ErrorCode = -1
		GOTO Cleanup
	END

	SET @CreateDate = @CurrentTimeUtc

	SELECT  @NewUserId = UserId FROM aspnet.Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
	IF ( @NewUserId IS NULL )
	BEGIN
		SET @NewUserId = @UserId
		EXEC @ReturnValue = aspnet.Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
		SET @NewUserCreated = 1
	END
	ELSE
	BEGIN
		SET @NewUserCreated = 0
		IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
		BEGIN
			SET @ErrorCode = 6
			GOTO Cleanup
		END
	END

	IF( @@ERROR <> 0 )
	BEGIN
		SET @ErrorCode = -1
		GOTO Cleanup
	END

	IF( @ReturnValue = -1 )
	BEGIN
		SET @ErrorCode = 10
		GOTO Cleanup
	END

	IF ( EXISTS ( SELECT UserId
				  FROM   aspnet.Membership
				  WHERE  @NewUserId = UserId ) )
	BEGIN
		SET @ErrorCode = 6
		GOTO Cleanup
	END

	SET @UserId = @NewUserId

	IF (@UniqueEmail = 1)
	BEGIN
		IF (EXISTS (SELECT *
					FROM  aspnet.Membership m WITH ( UPDLOCK, HOLDLOCK )
					WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
		BEGIN
			SET @ErrorCode = 7
			GOTO Cleanup
		END
	END

	IF (@NewUserCreated = 0)
	BEGIN
		UPDATE aspnet.Users
		SET    LastActivityDate = @CreateDate
		WHERE  @UserId = UserId
		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END
	END

	INSERT INTO aspnet.Membership
				( ApplicationId,
				  UserId,
				  Password,
				  PasswordSalt,
				  Email,
				  LoweredEmail,
				  PasswordQuestion,
				  PasswordAnswer,
				  PasswordFormat,
				  IsApproved,
				  IsLockedOut,
				  CreateDate,
				  LastLoginDate,
				  LastPasswordChangedDate,
				  LastLockoutDate,
				  FailedPasswordAttemptCount,
				  FailedPasswordAttemptWindowStart,
				  FailedPasswordAnswerAttemptCount,
				  FailedPasswordAnswerAttemptWindowStart )
		 VALUES ( @ApplicationId,
				  @UserId,
				  @Password,
				  @PasswordSalt,
				  @Email,
				  LOWER(@Email),
				  @PasswordQuestion,
				  @PasswordAnswer,
				  @PasswordFormat,
				  @IsApproved,
				  @IsLockedOut,
				  @CreateDate,
				  @CreateDate,
				  @CreateDate,
				  @LastLockoutDate,
				  @FailedPasswordAttemptCount,
				  @FailedPasswordAttemptWindowStart,
				  @FailedPasswordAnswerAttemptCount,
				  @FailedPasswordAnswerAttemptWindowStart )

	IF( @@ERROR <> 0 )
	BEGIN
		SET @ErrorCode = -1
		GOTO Cleanup
	END

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	RETURN 0

Cleanup:

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN @ErrorCode

END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetUserByName')))
DROP PROCEDURE aspnet.Membership_GetUserByName
GO

CREATE PROCEDURE aspnet.Membership_GetUserByName
	@ApplicationName      nvarchar(256),
	@UserName             nvarchar(256),
	@CurrentTimeUtc       datetime,
	@UpdateLastActivity   bit = 0
AS
BEGIN
	DECLARE @UserId uniqueidentifier

	IF (@UpdateLastActivity = 1)
	BEGIN
		-- select user ID from aspnet_users table
		SELECT TOP 1 @UserId = u.UserId
		FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
		WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
				u.ApplicationId = a.ApplicationId    AND
				LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

		IF (@@ROWCOUNT = 0) -- Username not found
			RETURN -1

		UPDATE   aspnet.Users
		SET      LastActivityDate = @CurrentTimeUtc
		WHERE    @UserId = UserId

		SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
				m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
				u.UserId, m.IsLockedOut, m.LastLockoutDate
		FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
		WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
	END
	ELSE
	BEGIN
		SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
				m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
				u.UserId, m.IsLockedOut,m.LastLockoutDate
		FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
		WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
				u.ApplicationId = a.ApplicationId    AND
				LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

		IF (@@ROWCOUNT = 0) -- Username not found
			RETURN -1
	END

	RETURN 0
END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetUserByUserId')))
DROP PROCEDURE aspnet.Membership_GetUserByUserId
GO

CREATE PROCEDURE aspnet.Membership_GetUserByUserId
	@UserId               uniqueidentifier,
	@CurrentTimeUtc       datetime,
	@UpdateLastActivity   bit = 0
AS
BEGIN
	IF ( @UpdateLastActivity = 1 )
	BEGIN
		UPDATE   aspnet.Users
		SET      LastActivityDate = @CurrentTimeUtc
		FROM     aspnet.Users
		WHERE    @UserId = UserId

		IF ( @@ROWCOUNT = 0 ) -- User ID not found
			RETURN -1
	END

	SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
			m.CreateDate, m.LastLoginDate, u.LastActivityDate,
			m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
			m.LastLockoutDate
	FROM    aspnet.Users u, aspnet.Membership m
	WHERE   @UserId = u.UserId AND u.UserId = m.UserId

	IF ( @@ROWCOUNT = 0 ) -- User ID not found
	   RETURN -1

	RETURN 0
END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetUserByEmail')))
DROP PROCEDURE aspnet.Membership_GetUserByEmail
GO

CREATE PROCEDURE aspnet.Membership_GetUserByEmail
	@ApplicationName  nvarchar(256),
	@Email            nvarchar(256)
AS
BEGIN
	IF( @Email IS NULL )
		SELECT  u.UserName
		FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
		WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
				u.ApplicationId = a.ApplicationId    AND
				u.UserId = m.UserId AND
				m.LoweredEmail IS NULL
	ELSE
		SELECT  u.UserName
		FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
		WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
				u.ApplicationId = a.ApplicationId    AND
				u.UserId = m.UserId AND
				LOWER(@Email) = m.LoweredEmail

	IF (@@rowcount = 0)
		RETURN(1)
	RETURN(0)
END
GO

/*************************************************************/
/*************************************************************/

IF ( EXISTS( SELECT ROUTINE_NAME
			 FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE ( ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetPasswordWithFormat')))
DROP PROCEDURE aspnet.Membership_GetPasswordWithFormat
GO

CREATE PROCEDURE aspnet.Membership_GetPasswordWithFormat
	@ApplicationName                nvarchar(256),
	@UserName                       nvarchar(256),
	@UpdateLastLoginActivityDate    bit,
	@CurrentTimeUtc                 datetime
AS
BEGIN
	DECLARE @IsLockedOut                        bit
	DECLARE @UserId                             uniqueidentifier
	DECLARE @Password                           nvarchar(128)
	DECLARE @PasswordSalt                       nvarchar(128)
	DECLARE @PasswordFormat                     int
	DECLARE @FailedPasswordAttemptCount         int
	DECLARE @FailedPasswordAnswerAttemptCount   int
	DECLARE @IsApproved                         bit
	DECLARE @LastActivityDate                   datetime
	DECLARE @LastLoginDate                      datetime

	SELECT  @UserId          = NULL

	SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
			@PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
			@FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
			@LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
	FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m
	WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.ApplicationId = a.ApplicationId    AND
			u.UserId = m.UserId AND
			LOWER(@UserName) = u.LoweredUserName

	IF (@UserId IS NULL)
		RETURN 1

	IF (@IsLockedOut = 1)
		RETURN 99

	SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
			 @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

	IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
	BEGIN
		UPDATE  aspnet.Membership
		SET     LastLoginDate = @CurrentTimeUtc
		WHERE   UserId = @UserId

		UPDATE  aspnet.Users
		SET     LastActivityDate = @CurrentTimeUtc
		WHERE   @UserId = UserId
	END


	RETURN 0
END
GO
/*************************************************************/
/*************************************************************/

IF ( EXISTS( SELECT ROUTINE_NAME
			 FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE ( ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_UpdateUserInfo')))
DROP PROCEDURE aspnet.Membership_UpdateUserInfo
GO

CREATE PROCEDURE aspnet.Membership_UpdateUserInfo
	@ApplicationName                nvarchar(256),
	@UserName                       nvarchar(256),
	@IsPasswordCorrect              bit,
	@UpdateLastLoginActivityDate    bit,
	@MaxInvalidPasswordAttempts     int,
	@PasswordAttemptWindow          int,
	@CurrentTimeUtc                 datetime,
	@LastLoginDate                  datetime,
	@LastActivityDate               datetime
AS
BEGIN
	DECLARE @UserId                                 uniqueidentifier
	DECLARE @IsApproved                             bit
	DECLARE @IsLockedOut                            bit
	DECLARE @LastLockoutDate                        datetime
	DECLARE @FailedPasswordAttemptCount             int
	DECLARE @FailedPasswordAttemptWindowStart       datetime
	DECLARE @FailedPasswordAnswerAttemptCount       int
	DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

	DECLARE @ErrorCode     int
	SET @ErrorCode = 0

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
		SET @TranStarted = 0

	SELECT  @UserId = u.UserId,
			@IsApproved = m.IsApproved,
			@IsLockedOut = m.IsLockedOut,
			@LastLockoutDate = m.LastLockoutDate,
			@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
			@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
			@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
			@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
	FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m WITH ( UPDLOCK )
	WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.ApplicationId = a.ApplicationId    AND
			u.UserId = m.UserId AND
			LOWER(@UserName) = u.LoweredUserName

	IF ( @@rowcount = 0 )
	BEGIN
		SET @ErrorCode = 1
		GOTO Cleanup
	END

	IF( @IsLockedOut = 1 )
	BEGIN
		GOTO Cleanup
	END

	IF( @IsPasswordCorrect = 0 )
	BEGIN
		IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
		BEGIN
			SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
			SET @FailedPasswordAttemptCount = 1
		END
		ELSE
		BEGIN
			SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
			SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
		END

		BEGIN
			IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
			BEGIN
				SET @IsLockedOut = 1
				SET @LastLockoutDate = @CurrentTimeUtc
			END
		END
	END
	ELSE
	BEGIN
		IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
		BEGIN
			SET @FailedPasswordAttemptCount = 0
			SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
			SET @FailedPasswordAnswerAttemptCount = 0
			SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
			SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
		END
	END

	IF( @UpdateLastLoginActivityDate = 1 )
	BEGIN
		UPDATE  aspnet.Users
		SET     LastActivityDate = @LastActivityDate
		WHERE   @UserId = UserId

		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END

		UPDATE  aspnet.Membership
		SET     LastLoginDate = @LastLoginDate
		WHERE   UserId = @UserId

		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END
	END


	UPDATE aspnet.Membership
	SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
		FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
		FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
		FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
		FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
	WHERE @UserId = UserId

	IF( @@ERROR <> 0 )
	BEGIN
		SET @ErrorCode = -1
		GOTO Cleanup
	END

	IF( @TranStarted = 1 )
	BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
	END

	RETURN @ErrorCode

Cleanup:

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN @ErrorCode

END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetPassword')))
DROP PROCEDURE aspnet.Membership_GetPassword
GO

CREATE PROCEDURE aspnet.Membership_GetPassword
	@ApplicationName                nvarchar(256),
	@UserName                       nvarchar(256),
	@MaxInvalidPasswordAttempts     int,
	@PasswordAttemptWindow          int,
	@CurrentTimeUtc                 datetime,
	@PasswordAnswer                 nvarchar(128) = NULL
AS
BEGIN
	DECLARE @UserId                                 uniqueidentifier
	DECLARE @PasswordFormat                         int
	DECLARE @Password                               nvarchar(128)
	DECLARE @passAns                                nvarchar(128)
	DECLARE @IsLockedOut                            bit
	DECLARE @LastLockoutDate                        datetime
	DECLARE @FailedPasswordAttemptCount             int
	DECLARE @FailedPasswordAttemptWindowStart       datetime
	DECLARE @FailedPasswordAnswerAttemptCount       int
	DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

	DECLARE @ErrorCode     int
	SET @ErrorCode = 0

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
		SET @TranStarted = 0

	SELECT  @UserId = u.UserId,
			@Password = m.Password,
			@passAns = m.PasswordAnswer,
			@PasswordFormat = m.PasswordFormat,
			@IsLockedOut = m.IsLockedOut,
			@LastLockoutDate = m.LastLockoutDate,
			@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
			@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
			@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
			@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
	FROM    aspnet.Applications a, aspnet.Users u, aspnet.Membership m WITH ( UPDLOCK )
	WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.ApplicationId = a.ApplicationId    AND
			u.UserId = m.UserId AND
			LOWER(@UserName) = u.LoweredUserName

	IF ( @@rowcount = 0 )
	BEGIN
		SET @ErrorCode = 1
		GOTO Cleanup
	END

	IF( @IsLockedOut = 1 )
	BEGIN
		SET @ErrorCode = 99
		GOTO Cleanup
	END

	IF ( NOT( @PasswordAnswer IS NULL ) )
	BEGIN
		IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
		BEGIN
			IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
			BEGIN
				SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
				SET @FailedPasswordAnswerAttemptCount = 1
			END
			ELSE
			BEGIN
				SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
				SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
			END

			BEGIN
				IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
				BEGIN
					SET @IsLockedOut = 1
					SET @LastLockoutDate = @CurrentTimeUtc
				END
			END

			SET @ErrorCode = 3
		END
		ELSE
		BEGIN
			IF( @FailedPasswordAnswerAttemptCount > 0 )
			BEGIN
				SET @FailedPasswordAnswerAttemptCount = 0
				SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
			END
		END

		UPDATE aspnet.Membership
		SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
			FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
			FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
			FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
			FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
		WHERE @UserId = UserId

		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END
	END

	IF( @TranStarted = 1 )
	BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
	END

	IF( @ErrorCode = 0 )
		SELECT @Password, @PasswordFormat

	RETURN @ErrorCode

Cleanup:

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN @ErrorCode

END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_SetPassword')))
DROP PROCEDURE aspnet.Membership_SetPassword
GO

CREATE PROCEDURE aspnet.Membership_SetPassword
	@ApplicationName  nvarchar(256),
	@UserName         nvarchar(256),
	@NewPassword      nvarchar(128),
	@PasswordSalt     nvarchar(128),
	@CurrentTimeUtc   datetime,
	@PasswordFormat   int = 0
AS
BEGIN
	DECLARE @UserId uniqueidentifier
	SELECT  @UserId = NULL
	SELECT  @UserId = u.UserId
	FROM    aspnet.Users u, aspnet.Applications a, aspnet.Membership m
	WHERE   LoweredUserName = LOWER(@UserName) AND
			u.ApplicationId = a.ApplicationId  AND
			LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.UserId = m.UserId

	IF (@UserId IS NULL)
		RETURN(1)

	UPDATE aspnet.Membership
	SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
		LastPasswordChangedDate = @CurrentTimeUtc
	WHERE @UserId = UserId
	RETURN(0)
END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_ResetPassword')))
DROP PROCEDURE aspnet.Membership_ResetPassword
GO

CREATE PROCEDURE aspnet.Membership_ResetPassword
	@ApplicationName             nvarchar(256),
	@UserName                    nvarchar(256),
	@NewPassword                 nvarchar(128),
	@MaxInvalidPasswordAttempts  int,
	@PasswordAttemptWindow       int,
	@PasswordSalt                nvarchar(128),
	@CurrentTimeUtc              datetime,
	@PasswordFormat              int = 0,
	@PasswordAnswer              nvarchar(128) = NULL
AS
BEGIN
	DECLARE @IsLockedOut                            bit
	DECLARE @LastLockoutDate                        datetime
	DECLARE @FailedPasswordAttemptCount             int
	DECLARE @FailedPasswordAttemptWindowStart       datetime
	DECLARE @FailedPasswordAnswerAttemptCount       int
	DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

	DECLARE @UserId                                 uniqueidentifier
	SET     @UserId = NULL

	DECLARE @ErrorCode     int
	SET @ErrorCode = 0

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
		SET @TranStarted = 0

	SELECT  @UserId = u.UserId
	FROM    aspnet.Users u, aspnet.Applications a, aspnet.Membership m
	WHERE   LoweredUserName = LOWER(@UserName) AND
			u.ApplicationId = a.ApplicationId  AND
			LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.UserId = m.UserId

	IF ( @UserId IS NULL )
	BEGIN
		SET @ErrorCode = 1
		GOTO Cleanup
	END

	SELECT @IsLockedOut = IsLockedOut,
		   @LastLockoutDate = LastLockoutDate,
		   @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
		   @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
		   @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
		   @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
	FROM aspnet.Membership WITH ( UPDLOCK )
	WHERE @UserId = UserId

	IF( @IsLockedOut = 1 )
	BEGIN
		SET @ErrorCode = 99
		GOTO Cleanup
	END

	UPDATE aspnet.Membership
	SET    Password = @NewPassword,
		   LastPasswordChangedDate = @CurrentTimeUtc,
		   PasswordFormat = @PasswordFormat,
		   PasswordSalt = @PasswordSalt
	WHERE  @UserId = UserId AND
		   ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

	IF ( @@ROWCOUNT = 0 )
		BEGIN
			IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
			BEGIN
				SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
				SET @FailedPasswordAnswerAttemptCount = 1
			END
			ELSE
			BEGIN
				SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
				SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
			END

			BEGIN
				IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
				BEGIN
					SET @IsLockedOut = 1
					SET @LastLockoutDate = @CurrentTimeUtc
				END
			END

			SET @ErrorCode = 3
		END
	ELSE
		BEGIN
			IF( @FailedPasswordAnswerAttemptCount > 0 )
			BEGIN
				SET @FailedPasswordAnswerAttemptCount = 0
				SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
			END
		END

	IF( NOT ( @PasswordAnswer IS NULL ) )
	BEGIN
		UPDATE aspnet.Membership
		SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
			FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
			FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
			FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
			FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
		WHERE @UserId = UserId

		IF( @@ERROR <> 0 )
		BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END
	END

	IF( @TranStarted = 1 )
	BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
	END

	RETURN @ErrorCode

Cleanup:

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN @ErrorCode

END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_UnlockUser')))
DROP PROCEDURE aspnet.Membership_UnlockUser
GO

CREATE PROCEDURE aspnet.Membership_UnlockUser
	@ApplicationName                         nvarchar(256),
	@UserName                                nvarchar(256)
AS
BEGIN
	DECLARE @UserId uniqueidentifier
	SELECT  @UserId = NULL
	SELECT  @UserId = u.UserId
	FROM    aspnet.Users u, aspnet.Applications a, aspnet.Membership m
	WHERE   LoweredUserName = LOWER(@UserName) AND
			u.ApplicationId = a.ApplicationId  AND
			LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.UserId = m.UserId

	IF ( @UserId IS NULL )
		RETURN 1

	UPDATE aspnet.Membership
	SET IsLockedOut = 0,
		FailedPasswordAttemptCount = 0,
		FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
		FailedPasswordAnswerAttemptCount = 0,
		FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
		LastLockoutDate = CONVERT( datetime, '17540101', 112 )
	WHERE @UserId = UserId

	RETURN 0
END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_UpdateUser')))
DROP PROCEDURE aspnet.Membership_UpdateUser
GO

CREATE PROCEDURE aspnet.Membership_UpdateUser
	@ApplicationName      nvarchar(256),
	@UserName             nvarchar(256),
	@Email                nvarchar(256),
	@Comment              ntext,
	@IsApproved           bit,
	@LastLoginDate        datetime,
	@LastActivityDate     datetime,
	@UniqueEmail          int,
	@CurrentTimeUtc       datetime
AS
BEGIN
	DECLARE @UserId uniqueidentifier
	DECLARE @ApplicationId uniqueidentifier
	SELECT  @UserId = NULL
	SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
	FROM    aspnet.Users u, aspnet.Applications a, aspnet.Membership m
	WHERE   LoweredUserName = LOWER(@UserName) AND
			u.ApplicationId = a.ApplicationId  AND
			LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.UserId = m.UserId

	IF (@UserId IS NULL)
		RETURN(1)

	IF (@UniqueEmail = 1)
	BEGIN
		IF (EXISTS (SELECT *
					FROM  aspnet.Membership WITH (UPDLOCK, HOLDLOCK)
					WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
		BEGIN
			RETURN(7)
		END
	END

	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END
	ELSE
	SET @TranStarted = 0

	UPDATE aspnet.Users WITH (ROWLOCK)
	SET
		 LastActivityDate = @LastActivityDate
	WHERE
	   @UserId = UserId

	IF( @@ERROR <> 0 )
		GOTO Cleanup

	UPDATE aspnet.Membership WITH (ROWLOCK)
	SET
		 Email            = @Email,
		 LoweredEmail     = LOWER(@Email),
		 Comment          = @Comment,
		 IsApproved       = @IsApproved,
		 LastLoginDate    = @LastLoginDate
	WHERE
	   @UserId = UserId

	IF( @@ERROR <> 0 )
		GOTO Cleanup

	IF( @TranStarted = 1 )
	BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
	END

	RETURN 0

Cleanup:

	IF( @TranStarted = 1 )
	BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RETURN -1
END
GO

/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_ChangePasswordQuestionAndAnswer')))
DROP PROCEDURE aspnet.Membership_ChangePasswordQuestionAndAnswer
GO

CREATE PROCEDURE aspnet.Membership_ChangePasswordQuestionAndAnswer
	@ApplicationName       nvarchar(256),
	@UserName              nvarchar(256),
	@NewPasswordQuestion   nvarchar(256),
	@NewPasswordAnswer     nvarchar(128)
AS
BEGIN
	DECLARE @UserId uniqueidentifier
	SELECT  @UserId = NULL
	SELECT  @UserId = u.UserId
	FROM    aspnet.Membership m, aspnet.Users u, aspnet.Applications a
	WHERE   LoweredUserName = LOWER(@UserName) AND
			u.ApplicationId = a.ApplicationId  AND
			LOWER(@ApplicationName) = a.LoweredApplicationName AND
			u.UserId = m.UserId
	IF (@UserId IS NULL)
	BEGIN
		RETURN(1)
	END

	UPDATE aspnet.Membership
	SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
	WHERE  UserId=@UserId
	RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetAllUsers')))
DROP PROCEDURE aspnet.Membership_GetAllUsers
GO

CREATE PROCEDURE aspnet.Membership_GetAllUsers
	@ApplicationName       nvarchar(256),
	@PageIndex             int,
	@PageSize              int
AS
BEGIN
	DECLARE @ApplicationId uniqueidentifier
	SELECT  @ApplicationId = NULL
	SELECT  @ApplicationId = ApplicationId FROM aspnet.Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@ApplicationId IS NULL)
		RETURN 0


	-- Set the page bounds
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @TotalRecords   int
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

	-- Create a temp table TO store the select results
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0, 1) NOT NULL,
		UserId uniqueidentifier
	)

	-- Insert into our temp table
	INSERT INTO #PageIndexForUsers (UserId)
	SELECT u.UserId
	FROM   aspnet.Membership m, aspnet.Users u
	WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
	ORDER BY u.UserName

	SELECT @TotalRecords = @@ROWCOUNT

	SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
			m.CreateDate,
			m.LastLoginDate,
			u.LastActivityDate,
			m.LastPasswordChangedDate,
			u.UserId, m.IsLockedOut,
			m.LastLockoutDate
	FROM   aspnet.Membership m, aspnet.Users u, #PageIndexForUsers p
	WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
		   p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY u.UserName
	RETURN @TotalRecords
END
GO
/*************************************************************/
/*************************************************************/

IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_GetNumberOfUsersOnline')))
DROP PROCEDURE aspnet.Membership_GetNumberOfUsersOnline
GO

CREATE PROCEDURE aspnet.Membership_GetNumberOfUsersOnline
	@ApplicationName            nvarchar(256),
	@MinutesSinceLastInActive   int,
	@CurrentTimeUtc             datetime
AS
BEGIN
	DECLARE @DateActive datetime
	SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

	DECLARE @NumOnline int
	SELECT  @NumOnline = COUNT(*)
	FROM    aspnet.Users u(NOLOCK),
			aspnet.Applications a(NOLOCK),
			aspnet.Membership m(NOLOCK)
	WHERE   u.ApplicationId = a.ApplicationId                  AND
			LastActivityDate > @DateActive                     AND
			a.LoweredApplicationName = LOWER(@ApplicationName) AND
			u.UserId = m.UserId
	RETURN(@NumOnline)
END
GO


/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_FindUsersByName')))
DROP PROCEDURE aspnet.Membership_FindUsersByName
GO

CREATE PROCEDURE aspnet.Membership_FindUsersByName
	@ApplicationName       nvarchar(256),
	@UserNameToMatch       nvarchar(256),
	@PageIndex             int,
	@PageSize              int
AS
BEGIN
	DECLARE @ApplicationId uniqueidentifier
	SELECT  @ApplicationId = NULL
	SELECT  @ApplicationId = ApplicationId FROM aspnet.Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@ApplicationId IS NULL)
		RETURN 0

	-- Set the page bounds
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @TotalRecords   int
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

	-- Create a temp table TO store the select results
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0, 1) NOT NULL,
		UserId uniqueidentifier
	)

	-- Insert into our temp table
	INSERT INTO #PageIndexForUsers (UserId)
		SELECT u.UserId
		FROM   aspnet.Users u, aspnet.Membership m
		WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
		ORDER BY u.UserName


	SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
			m.CreateDate,
			m.LastLoginDate,
			u.LastActivityDate,
			m.LastPasswordChangedDate,
			u.UserId, m.IsLockedOut,
			m.LastLockoutDate
	FROM   aspnet.Membership m, aspnet.Users u, #PageIndexForUsers p
	WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
		   p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY u.UserName

	SELECT  @TotalRecords = COUNT(*)
	FROM    #PageIndexForUsers
	RETURN @TotalRecords
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT ROUTINE_NAME
			  FROM INFORMATION_SCHEMA.ROUTINES
			 WHERE (ROUTINE_SCHEMA = N'aspnet' AND ROUTINE_NAME = N'Membership_FindUsersByEmail')))
DROP PROCEDURE aspnet.Membership_FindUsersByEmail
GO

CREATE PROCEDURE aspnet.Membership_FindUsersByEmail
	@ApplicationName       nvarchar(256),
	@EmailToMatch          nvarchar(256),
	@PageIndex             int,
	@PageSize              int
AS
BEGIN
	DECLARE @ApplicationId uniqueidentifier
	SELECT  @ApplicationId = NULL
	SELECT  @ApplicationId = ApplicationId FROM aspnet.Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@ApplicationId IS NULL)
		RETURN 0

	-- Set the page bounds
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @TotalRecords   int
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

	-- Create a temp table TO store the select results
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0, 1) NOT NULL,
		UserId uniqueidentifier
	)

	-- Insert into our temp table
	IF( @EmailToMatch IS NULL )
		INSERT INTO #PageIndexForUsers (UserId)
			SELECT u.UserId
			FROM   aspnet.Users u, aspnet.Membership m
			WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
			ORDER BY m.LoweredEmail
	ELSE
		INSERT INTO #PageIndexForUsers (UserId)
			SELECT u.UserId
			FROM   aspnet.Users u, aspnet.Membership m
			WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
			ORDER BY m.LoweredEmail

	SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
			m.CreateDate,
			m.LastLoginDate,
			u.LastActivityDate,
			m.LastPasswordChangedDate,
			u.UserId, m.IsLockedOut,
			m.LastLockoutDate
	FROM   aspnet.Membership m, aspnet.Users u, #PageIndexForUsers p
	WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
		   p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY m.LoweredEmail

	SELECT  @TotalRecords = COUNT(*)
	FROM    #PageIndexForUsers
	RETURN @TotalRecords
END
GO

/*************************************************************/
/*************************************************************/

IF (NOT EXISTS (SELECT TABLE_NAME
				FROM INFORMATION_SCHEMA.VIEWS
				WHERE (TABLE_SCHEMA = N'aspnet' AND TABLE_NAME = N'MembershipUsers')))
BEGIN
  PRINT 'Creating the aspnet.MembershipUsers view...'
  EXEC('
  CREATE VIEW [aspnet].[MembershipUsers]
  AS SELECT [aspnet].[Membership].[UserId],
			[aspnet].[Membership].[PasswordFormat],
			[aspnet].[Membership].[MobilePIN],
			[aspnet].[Membership].[Email],
			[aspnet].[Membership].[LoweredEmail],
			[aspnet].[Membership].[PasswordQuestion],
			[aspnet].[Membership].[PasswordAnswer],
			[aspnet].[Membership].[IsApproved],
			[aspnet].[Membership].[IsLockedOut],
			[aspnet].[Membership].[CreateDate],
			[aspnet].[Membership].[LastLoginDate],
			[aspnet].[Membership].[LastPasswordChangedDate],
			[aspnet].[Membership].[LastLockoutDate],
			[aspnet].[Membership].[FailedPasswordAttemptCount],
			[aspnet].[Membership].[FailedPasswordAttemptWindowStart],
			[aspnet].[Membership].[FailedPasswordAnswerAttemptCount],
			[aspnet].[Membership].[FailedPasswordAnswerAttemptWindowStart],
			[aspnet].[Membership].[Comment],
			[aspnet].[Users].[ApplicationId],
			[aspnet].[Users].[UserName],
			[aspnet].[Users].[MobileAlias],
			[aspnet].[Users].[IsAnonymous],
			[aspnet].[Users].[LastActivityDate]
  FROM [aspnet].[Membership] INNER JOIN [aspnet].[Users]
	  ON [aspnet].[Membership].[UserId] = [aspnet].[Users].[UserId]
  ')
END
GO

/*************************************************************/
/*************************************************************/

--
--Create Membership schema version
--
DECLARE @command nvarchar(4000)
SET @command = 'GRANT EXECUTE ON [aspnet].RegisterSchemaVersion TO ' + QUOTENAME(user)
EXECUTE (@command)
GO

EXEC [aspnet].RegisterSchemaVersion N'Membership', N'1', 1, 1
GO

/*************************************************************/
/*************************************************************/

--
--Create Membership roles
--

IF ( NOT EXISTS ( SELECT name
				  FROM sysusers
				  WHERE issqlrole = 1
				  AND name = N'aspnet_Membership_FullAccess'  ) )
EXEC sp_addrole N'aspnet_Membership_FullAccess'

IF ( NOT EXISTS ( SELECT name
				  FROM sysusers
				  WHERE issqlrole = 1
				  AND name = N'aspnet_Membership_BasicAccess'  ) )
EXEC sp_addrole N'aspnet_Membership_BasicAccess'

IF ( NOT EXISTS ( SELECT name
				  FROM sysusers
				  WHERE issqlrole = 1
				  AND name = N'aspnet_Membership_ReportingAccess'  ) )
EXEC sp_addrole N'aspnet_Membership_ReportingAccess'
GO

EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'
GO

--
--Stored Procedure rights for BasicAcess
--
GRANT EXECUTE ON aspnet.Membership_GetUserByUserId TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_GetUserByName TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_GetUserByEmail TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_GetPassword TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.CheckSchemaVersion TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.RegisterSchemaVersion TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON aspnet.UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess

--
--Stored Procedure rights for ReportingAccess
--
GRANT EXECUTE ON aspnet.Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_GetUserByName TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_GetAllUsers TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_FindUsersByName TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.CheckSchemaVersion TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.RegisterSchemaVersion TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON aspnet.UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess

--
--Additional stored procedure rights for FullAccess
--
GRANT EXECUTE ON aspnet.Users_DeleteUser TO aspnet_Membership_FullAccess

GRANT EXECUTE ON aspnet.Membership_CreateUser TO aspnet_Membership_FullAccess
GRANT EXECUTE ON aspnet.Membership_SetPassword TO aspnet_Membership_FullAccess
GRANT EXECUTE ON aspnet.Membership_ResetPassword TO aspnet_Membership_FullAccess
GRANT EXECUTE ON aspnet.Membership_UpdateUser TO aspnet_Membership_FullAccess
GRANT EXECUTE ON aspnet.Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess
GRANT EXECUTE ON aspnet.Membership_UnlockUser TO aspnet_Membership_FullAccess

--
--View rights
--
GRANT SELECT ON aspnet.ApplicationsView TO aspnet_Membership_ReportingAccess
GRANT SELECT ON aspnet.UsersView TO aspnet_Membership_ReportingAccess

GRANT SELECT ON aspnet.MembershipUsers TO aspnet_Membership_ReportingAccess

/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/

DECLARE @command nvarchar(4000)
SET @command = 'REVOKE EXECUTE ON [aspnet].RegisterSchemaVersion FROM ' + QUOTENAME(user)
EXECUTE (@command)
GO

PRINT '--------------------------------------------'
PRINT 'Completed execution of InstallMembership.SQL'
PRINT '--------------------------------------------'
